﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using YMImportWeb.Common;
using YMImportWeb.Model;

namespace YMImportWeb.DAL
{
    public class DataDAL
    {
        /// <summary>
        /// 根据user_identityID获取用户信息
        /// </summary>
        public static OrgUser GetOrgUserByUserId(int userId)
        {
            string strSql = @"select top 1 u.user_id,u.user_identityID,u.user_code,u.user_name,u.user_account,up.position_id,p.dept_id,d.dept_name 
from[Macrowing.Organization].[dbo].[org_user] as u left join[Macrowing.Organization].[dbo].[org_userPosition] as up on up.user_id=u.user_id 
left join[Macrowing.Organization].[dbo].[org_position] as p on p.position_id=up.position_id 
left join[Macrowing.Organization].[dbo].[org_department] as d on d.dept_id=p.dept_id 
where u.user_identityID=@userId and up.main=1";
            SqlParameter[] parameters = {
                    new SqlParameter("@userId", userId)
            };
            DataTable dt = DBHelper.GetDataTable(strSql, parameters);
            var orgUserList = dt.ConvertTo<OrgUser>();
            if (orgUserList.Count()>0)
            {
                return orgUserList.ToList()[0];
            }
            else
            {
                return new OrgUser();
            }
        }

        #region 归档人工号
        /// <summary>
        /// 根据归档人工号获取用户信息
        /// </summary>
        public static OrgUser GetOrgUserByCode(string userCode)
        {
            string strSql = @"select top 1 u.user_id,u.user_identityID,u.user_code,u.user_name,up.position_id,p.dept_id,d.dept_name 
from [Macrowing.Organization].[dbo].[org_user] as u left join [Macrowing.Organization].[dbo].[org_userPosition] as up on up.user_id=u.user_id 
left join [Macrowing.Organization].[dbo].[org_position] as p on p.position_id=up.position_id 
left join [Macrowing.Organization].[dbo].[org_department] as d on d.dept_id=p.dept_id 
where u.user_code=@userCode and up.main=1";
            SqlParameter[] parameters = {
                    new SqlParameter("@userCode", userCode)
            };
            DataTable dt = DBHelper.GetDataTable(strSql, parameters);
            var orgUserList = dt.ConvertTo<OrgUser>();
            if (orgUserList.Count() > 0)
            {
                return orgUserList.ToList()[0];
            }
            else
            {
                return new OrgUser();
            }
        }

        /// <summary>
        /// 根据归档人工号查询是否存在
        /// </summary>
        public static int GetOrgUserCountByCode(string userCode)
        {
            string strSql = "select count(1) from [Macrowing.Organization].[dbo].[org_user] where user_code=@userCode";
            SqlParameter[] parameters = {
                    new SqlParameter("@userCode", userCode)
            };
            object obj = DBHelper.ExecuteScalar(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }
        #endregion

        #region 我方
        /// <summary>
        /// 根据我方查询是否存在
        /// </summary>
        public static int GetWXGLBaseWFCountByItem(string item)
        {
            string strSql = "select count(1) from [Edoc2.Form].[dbo].[edoc2_release_WXGL_base] where type='签约子公司' and item=@item";
            SqlParameter[] parameters = {
                    new SqlParameter("@item", item)
            };
            object obj = DBHelper.ExecuteScalar(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }
        /// <summary>
        /// 根据我方获取Id
        /// </summary>
        public static WXGLBase GetWXGLBaseWFByItem(string item)
        {
            string strSql = "select Id from [Edoc2.Form].[dbo].[edoc2_release_WXGL_base] where type='签约子公司' and item=@item";
            SqlParameter[] parameters = {
                    new SqlParameter("@item", item)
            };
            DataTable dt = DBHelper.GetDataTable(strSql, parameters);
            var wfList = dt.ConvertTo<WXGLBase>();
            if (wfList.Count() > 0)
            {
                return wfList.ToList()[0];
            }
            else
            {
                return new WXGLBase();
            }
        }
        #endregion

        #region 客户1
        /// <summary>
        /// 根据客户1查询是否存在
        /// </summary>
        public static int GetWXGLBaseKH1CountByItem(string item)
        {
            string strSql = "select count(1) from [Edoc2.Form].[dbo].[edoc2_release_WXGL_base] where type='客户' and item=@item";
            SqlParameter[] parameters = {
                    new SqlParameter("@item", item)
            };
            object obj = DBHelper.ExecuteScalar(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }
        /// <summary>
        /// 根据客户1获取Id
        /// </summary>
        public static WXGLBase GetWXGLBaseKH1ByItem(string item)
        {
            string strSql = "select Id from [Edoc2.Form].[dbo].[edoc2_release_WXGL_base] where type='客户' and item=@item";
            SqlParameter[] parameters = {
                    new SqlParameter("@item", item)
            };
            DataTable dt = DBHelper.GetDataTable(strSql, parameters);
            var kh1List = dt.ConvertTo<WXGLBase>();
            if (kh1List.Count() > 0)
            {
                return kh1List.ToList()[0];
            }
            else
            {
                return new WXGLBase();
            }
        }
        #endregion

        /// <summary>
        /// 根据职位Id获取部门信息
        /// </summary>
        public static OrgDepartment GetOrgDepartmentByPositionId(string positionId)
        {
            string strSql = @"select top 1 dept_id,dept_name from [Macrowing.Organization].[dbo].[org_department] 
where dept_id=(select dept_id from [Macrowing.Organization].[dbo].[org_position] 
where position_id=(select position_parentId from [Macrowing.Organization].[dbo].[org_position]
where position_id=(select position_parentId from [Macrowing.Organization].[dbo].[org_position]
where position_id=@position_id)))";
            SqlParameter[] parameters = {
                    new SqlParameter("@position_id", positionId)
            };
            DataTable dt = DBHelper.GetDataTable(strSql, parameters);
            var orgDepartmentList = dt.ConvertTo<OrgDepartment>();
            if (orgDepartmentList.Count() > 0)
            {
                return orgDepartmentList.ToList()[0];
            }
            else
            {
                return new OrgDepartment();
            }
        }

        /// <summary>
        /// 根据密级获取Id
        /// </summary>
        public static WXGLBase GetWXGLBaseByItem(string item)
        {
            string strSql = @"select Id from [Edoc2.Form].[dbo].[edoc2_release_WXGL_base] where type='密级' and item=@item";
            SqlParameter[] parameters = {
                    new SqlParameter("@item", item)
            };
            DataTable dt = DBHelper.GetDataTable(strSql, parameters);
            var orgBaseList = dt.ConvertTo<WXGLBase>();
            if (orgBaseList.Count() > 0)
            {
                return orgBaseList.ToList()[0];
            }
            else
            {
                return new WXGLBase();
            }
        }
        /// <summary>
        /// 根据密级查询是否存在
        /// </summary>
        public static int GetWXGLBaseCountByItem(string item)
        {
            string strSql = "select count(1) from [Edoc2.Form].[dbo].[edoc2_release_WXGL_base] where type='密级' and item=@item";
            SqlParameter[] parameters = {
                    new SqlParameter("@item", item)
            };
            object obj = DBHelper.ExecuteScalar(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }

        #region 保管期限
        /// <summary>
        /// 根据保管期限获取Id
        /// </summary>
        public static AccessSaveYear GetAccessSaveYearByTime(string time)
        {
            string strSql = @"select Id from [Edoc2.Form].[dbo].[edoc2_release_WXGL_AccessSaveYear] where [time]=@time";
            SqlParameter[] parameters = {
                    new SqlParameter("@time", time)
            };
            DataTable dt = DBHelper.GetDataTable(strSql, parameters);
            var orgSaveYearList = dt.ConvertTo<AccessSaveYear>();
            if (orgSaveYearList.Count() > 0)
            {
                return orgSaveYearList.ToList()[0];
            }
            else
            {
                return new AccessSaveYear();
            }
        }
        /// <summary>
        /// 根据保管期限查询是否存在
        /// </summary>
        public static int GetAccessSaveYearCountByTime(string time)
        {
            string strSql = "select count(1) from [Edoc2.Form].[dbo].[edoc2_release_WXGL_AccessSaveYear] where [time]=@time";
            SqlParameter[] parameters = {
                    new SqlParameter("@time", time)
            };
            object obj = DBHelper.ExecuteScalar(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }
        #endregion


        /// <summary>
        /// 根据档号查询是否存在
        /// </summary>
        public static int GetAccessArchiveInfoByRecord(string record)
        {
            string strSql = "select count(1) from [Edoc2.Form].[dbo].[edoc2_release_WXGL_Access_Archive] where Record=@Record";
            SqlParameter[] parameters = {
                    new SqlParameter("@Record", record)
            };
            object obj = DBHelper.ExecuteScalar(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }

        /// <summary>
        /// 写入归档
        /// </summary>
        internal static int InsertAccessArchive(AccessArchive model)
        {
            string strSql = @"insert into [Edoc2.Form].[dbo].[edoc2_release_WXGL_Access_Archive] (Id,createTime,modifiedTime,createId,updateId,people,Department,
AccessionDate,[file],Record,folderUrl,[level],[Page],InventoryStatus,dates,ExecutionDate,remarks,TotalInventory,ExistingInventory,[State],InventoryType,BatchNo,SaveTime,
OperationPeople,Unit,UnitId,ContractName,ProjectName,Number,ContractNo,JiaFang,ProjectNo,YiFang,IsCopy,Code,ArchiveWay,SafekeepingTime,BingFang) 
values (@Id,@createTime,@modifiedTime,@createId,@updateId,@people,@Department,@AccessionDate,@file,@Record,@folderUrl,@level,@Page,@InventoryStatus,@dates,@ExecutionDate,
@remarks,@TotalInventory,@ExistingInventory,@State,@InventoryType,@BatchNo,@SaveTime,@OperationPeople,@Unit,@UnitId,@ContractName,@ProjectName,@Number,@ContractNo,@JiaFang,
@ProjectNo,@YiFang,@IsCopy,@Code,@ArchiveWay,@SafekeepingTime,@BingFang)";
            SqlParameter[] parameters = {
                    new SqlParameter("@Id", model.Id),
                    new SqlParameter("@createTime", model.createTime),
                    new SqlParameter("@modifiedTime", model.modifiedTime),
                    new SqlParameter("@createId", model.createId),
                    new SqlParameter("@updateId", model.updateId),
                    new SqlParameter("@people", model.people),
                    new SqlParameter("@Department", model.Department),
                    new SqlParameter("@AccessionDate", model.AccessionDate),
                    new SqlParameter("@file", model.file),
                    new SqlParameter("@Record", model.Record),
                    new SqlParameter("@folderUrl", model.folderUrl),
                    new SqlParameter("@level", model.level),
                    new SqlParameter("@Page", model.Page),
                    new SqlParameter("@InventoryStatus", model.InventoryStatus),
                    new SqlParameter("@dates", model.dates),
                    new SqlParameter("@ExecutionDate", model.ExecutionDate),
                    new SqlParameter("@remarks", model.remarks),
                    new SqlParameter("@TotalInventory", model.TotalInventory),
                    new SqlParameter("@ExistingInventory", model.ExistingInventory),
                    new SqlParameter("@State", model.State),
                    new SqlParameter("@InventoryType", model.InventoryType),
                    new SqlParameter("@BatchNo", model.BatchNo),
                    new SqlParameter("@SaveTime", model.SaveTime),
                    new SqlParameter("@OperationPeople", model.OperationPeople),
                    new SqlParameter("@Unit", model.Unit),
                    new SqlParameter("@UnitId", model.UnitId),
                    new SqlParameter("@ContractName", model.ContractName),
                    new SqlParameter("@ProjectName", model.ProjectName),
                    new SqlParameter("@Number", model.Number),
                    new SqlParameter("@ContractNo", model.ContractNo),
                    new SqlParameter("@JiaFang", model.JiaFang),
                    new SqlParameter("@ProjectNo", model.ProjectNo),
                    new SqlParameter("@YiFang", model.YiFang),
                    new SqlParameter("@IsCopy", model.IsCopy),
                    new SqlParameter("@Code", model.Code),
                    new SqlParameter("@ArchiveWay", model.ArchiveWay),
                    new SqlParameter("@SafekeepingTime", model.SafekeepingTime),
                    new SqlParameter("@BingFang", model.BingFang)
            };
            int res = DBHelper.ExecuteNonQuery(strSql, parameters);
            return res;
        }

        /// <summary>
        /// 写入操作日志
        /// </summary>
        internal static int InsertAccessOperationLog(AccessOperationLog model)
        {
            string strSql = @"insert into [Edoc2.Form].[dbo].[edoc2_release_WXGL_Access_OperationLog] (OperationDataId,OperationType,OperationPeople,OperationDataName,OperationEdit,OperationBack,createTime,BatchNo,BorrowPeople,ArchivePeople,BorrowPeopleDept,BorrowPeopleUnit,ArchivePeopleDept,ArchivePeopleUnit,ArchiveCode,BorrowCode) 
values (@OperationDataId,@OperationType,@OperationPeople,@OperationDataName,@OperationEdit,@OperationBack,@createTime,@BatchNo,@BorrowPeople,@ArchivePeople,@BorrowPeopleDept,@BorrowPeopleUnit,@ArchivePeopleDept,@ArchivePeopleUnit,@ArchiveCode,@BorrowCode)";
            SqlParameter[] parameters = {
                    new SqlParameter("@OperationDataId", model.OperationDataId),
                    new SqlParameter("@OperationType", model.OperationType),
                    new SqlParameter("@OperationPeople", model.OperationPeople),
                    new SqlParameter("@OperationDataName", model.OperationDataName),
                    new SqlParameter("@OperationEdit", model.OperationEdit),
                    new SqlParameter("@OperationBack", model.OperationBack),
                    new SqlParameter("@createTime", model.createTime),
                    new SqlParameter("@BatchNo", model.BatchNo),
                    new SqlParameter("@BorrowPeople", model.BorrowPeople),
                    new SqlParameter("@ArchivePeople", model.ArchivePeople),
                    new SqlParameter("@BorrowPeopleDept", model.BorrowPeopleDept),
                    new SqlParameter("@BorrowPeopleUnit", model.BorrowPeopleUnit),
                    new SqlParameter("@ArchivePeopleDept", model.ArchivePeopleDept),
                    new SqlParameter("@ArchivePeopleUnit", model.ArchivePeopleUnit),
                    new SqlParameter("@ArchiveCode", model.ArchiveCode),
                    new SqlParameter("@BorrowCode", model.BorrowCode)
            };
            int res = DBHelper.ExecuteNonQuery(strSql, parameters);
            return res;
        }
    }
}